Binary Relationship Type Constraints

Binary relationship type constraints#

In the previous lesson, we learned that there is a degree of relationship that exists between entities. However, sometimes this degree is affected by the constraints of the organization or a particular scenario. Consider, for example, a case where the company has a rule that each employee must work for exactly one department. In this and similar cases, we would like to describe this constraint in our schema. Such rules are usually called the “constraints” on the relationship types that exist in our schema.

These constraints limit the possible combinations of entities that may participate in the corresponding relationship set. There are two main types of binary relationship constraints: mapping cardinality and participation.

Let’s look at each one of them in detail below.

Mapping cardinality#

Mapping cardinality describes the maximum number of entities that a given entity can be associated with via a relationship. In this lesson, we consider only the cardinality constraint for the binary relationship. The possible cardinality for binary relationship types are One to One (1:1), One to Many (1:N), and Many to Many (M:N).

The one to one relationship#

Given two entity sets A and B, there is a one to one relationship between A and B if each entity in set A is associated with at most one entity in set B and vice versa.

An example of a 1:1 binary relationship is MANAGES, which relates a DEPARTMENT entity to the EMPLOYEE entity. This represents the mini-world constraints that at any point in time, an employee can manage at most one department and a department can have at most one manager. This is represented in the diagram below:

Cardinality for binary relationships is represented on ER diagrams by displaying 1, M, and N on the diamonds. For a one to one relationship, it will look like this:

The one to many relationship#

A one to many relationship set associates two entity sets A and B if each entity in A is associated with several entities in B however, each entity in B is associated with at most one entity in A. An example is that there are many employees working in a department, however, an employee can work for only one department. This can be seen below:

In the case of ER diagrams a 1:N relationship looks like this:

The many to many relationship#

A many to many relationship set associates two entity sets A and B if each entity in A is associated with several entities in B, and, each entity in B is associated with several entities in A. The relationship type WORKS_ON is of cardinality ratio M:N, because the mini-world rule is that an employee can work on several projects and a project can have several employees. The diagram below can clear up any confusion:

In the case of ER diagrams, an M:N relationship looks like this:

Participation#

The participation constraint specifies whether the existence of an entity depends on it being related to another entity via the relationship type.

Participation in a relationship set R by an entity set A, maybe.

There are two types of participation constraints:

Total participation:#

This specifies that each entity in the entity set must compulsorily participate in at least one relationship instance in that relationship set. That is why it is also known as mandatory participation. Total participation is represented using a double line between the entity set and relationship set.

Example:#

The double line between the PROJECT entity and relationship WORKS_ON signifies total participation. It specifies that each project must be assigned to at least one employee. In other words, without an EMPLOYEE entity, the PROJECT entity would not exist.

Partial participation:#

This specifies that each entity in the entity set may or may not participate in the relationship instance in that relationship set. That is why it is also known as optional participation. Partial participation is represented using a single line between an entity set and a relationship set.

Example:#

A single line between the entity EMPLOYEE and the relationship WORKS_ON signifies partial participation. It specifies that some employees may work on a project and some may not.

In the next lesson, we will take a look at relationships with attributes.